package top.wlink.bzgrowup.bzex;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

import static android.content.Context.MODE_PRIVATE;

public class SqliteHandlerx {
    private static final String TAG = "SqliteHandlerx";
    private final static String DATABASE = "bzgrowup_201709.db";
    private static final String SETTINGSTABLE = "settings";
    private static final String RECORDTABLE = "records";

    private static SqliteHandlerx instance;

    public static synchronized SqliteHandlerx getInstance() {
        if (instance == null) {
            instance = new SqliteHandlerx();
        }
        return instance;
    }
    private SqliteHandlerx() { }

    public boolean runSqlBatch(List<String> aRunSql) {
        SQLiteDatabase db = null;
        try {
            db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MODE_PRIVATE, null);
            db.beginTransaction();  //手动设置开始事务
            for(int i = 0; i<aRunSql.size(); i++){
                db.execSQL(aRunSql.get(i));
            }
            db.setTransactionSuccessful();  // 不设置成功，调用endTransaction的时候会回滚。
            return true;
        }
        catch (Exception e){
            e.printStackTrace();
        }
        finally{
            if (null != db) db.endTransaction(); //处理完成
        }
        return false;
    }

    public boolean createTable() {
        Boolean lb_rtn = true;

        try {

            String ls_sqlDropTableRaw = "drop table if exists %s;";

            String ls_sqlDropSettings = String.format(Locale.CHINA, ls_sqlDropTableRaw, SETTINGSTABLE);
            String ls_sqlCreateSettingsRaw = "create table if not exists %s (id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "kind varchar(4),title varchar(100),timer varchar(4),tick numberic);";
            String ls_sqlCreateSettings =  String.format(Locale.CHINA, ls_sqlCreateSettingsRaw, SETTINGSTABLE);

            String ls_sqlDropRecords = String.format(Locale.CHINA, ls_sqlDropTableRaw, RECORDTABLE);
            String ls_sqlCreateRecordsRaw ="create table if not exists %s (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT,pid INTEGER,title varchar(100),kind varchar(4),timer varchar(4)," +
                    "remark varchar(400),status varchar(4),tasktime datetime,rectime datetime,timeout datetime);";
            String ls_sqlCreateRecords =  String.format(Locale.CHINA,ls_sqlCreateRecordsRaw, RECORDTABLE);
            /* DATETIME的TEXT格式为 "YYYY-MM-DD HH:MM:SS.SSS" 的日期。*/

            SQLiteDatabase db = null;
            try {
                db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MODE_PRIVATE, null);
                db.execSQL(ls_sqlDropSettings);
                db.execSQL(ls_sqlCreateSettings);
                db.execSQL(ls_sqlDropRecords);
                db.execSQL(ls_sqlCreateRecords);
            }
            catch (Exception e){
                e.printStackTrace();
                lb_rtn = false;
            }
            finally {
                if (null != db )db.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
            lb_rtn = false;
        }
        return lb_rtn;
    }



    //final Type type = new TypeToken<Map<String, Object>>(){}.getType();
    public List<ContentValues> getSqlResult(String aSql, String[] aVal) {
        SQLiteDatabase db = null;
        Cursor cur = null;
        List<ContentValues> retVal = new ArrayList<>();
        try {
            db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MODE_PRIVATE, null);
            cur = db.rawQuery(aSql, aVal);
            ContentValues map;  //stackoverflow.com/questions/7932420/android-sqlite-cursor-contentvalues
            if (cur.moveToFirst()) {
                do {
                    map = new ContentValues();
                    DatabaseUtils.cursorRowToContentValues(cur, map);
                    retVal.add(map);
                } while (cur.moveToNext());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != cur) cur.close();
            if (null != db) db.close();
        }
        return retVal;
    }

    /*StringBuilder sb = new StringBuilder("");
    for (ContentValues cv: retVal){
        for (Map.Entry<String, Object> item : cv.valueSet()) {
            sb.append( item.getKey() ).append(":").append(item.getValue().toString()).append(",");
        }
        sb.append("  ");
    }
    return sb.toString();
    */

    public void runSql(String aSql, String[] aVal) {
        SQLiteDatabase db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MODE_PRIVATE, null);
        if (null == aVal) db.execSQL(aSql); else db.execSQL(aSql, aVal);
        db.close();
        // final String MY_QUERY = "SELECT last_insert_rowid()";
    }


    void saveLocaleCache(LocalCache obj) {
        SQLiteDatabase db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MODE_PRIVATE, null);
        db.execSQL("INSERT INTO cache_record(kind,code,name,excode,ex,num1,num2) VALUES (?,?,?,?,?,?,?)",
                new String[]{obj.kind, String.valueOf(obj.code), obj.name, obj.excode,obj.ex,
                        String.valueOf(obj.num1), String.valueOf(obj.num2)});
        db.close();
        // final String MY_QUERY = "SELECT last_insert_rowid()";
    }

    public ArrayList<LocalCache> queryLocalCacheList(String aKind)
    {
        ArrayList<LocalCache> l_rtn = new ArrayList<>();

        SQLiteDatabase db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MODE_PRIVATE, null);
        Cursor c = db.rawQuery("SELECT * FROM cache_record where kind=?", new String[]{aKind});
        while (c.moveToNext()) {
            LocalCache info = new LocalCache();
            setLocalCacheRecord(c, info);
            l_rtn.add(info);
        }
        c.close();
        db.close();
        return l_rtn;
    }
    public LocalCache queryLocalCache(String aKind, int aCode)
    {
        SQLiteDatabase db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MODE_PRIVATE, null);
        Cursor c = db.rawQuery("SELECT * FROM cache_record where kind=? and code=?",  new String[]{aKind, String.valueOf(aCode) });
        LocalCache info = new LocalCache();

        if (c.moveToNext()) {
            setLocalCacheRecord(c, info);
        }else{ info = null; }
        c.close();
        db.close();
        return info;
    }
    private void setLocalCacheRecord(Cursor aCur,  LocalCache aCache){
        aCache.kind = aCur.getString(aCur.getColumnIndex("kind"));
        aCache.code = aCur.getInt(aCur.getColumnIndex("code"));       //
        aCache.name = aCur.getString(aCur.getColumnIndex("name"));
        aCache.num1 = aCur.getDouble(aCur.getColumnIndex("num1"));
        aCache.num2 = aCur.getDouble(aCur.getColumnIndex("num2"));
        aCache.ex = aCur.getString(aCur.getColumnIndex("ex"));
    }
}





	/*
	public  void saveParcel(Parcelx obj)
	{
		SQLiteDatabase db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MyApp.getInstance().getApplicationContext().MODE_PRIVATE, null);
		//db.execSQL("delete from YH_PORTAL", new Object[]{});
		db.execSQL("INSERT INTO YH_PORTAL(name, birth) VALUES (?,?)", new String[]{ obj.name, String.valueOf(obj.birth) } );
		db.close();
        // final String MY_QUERY = "SELECT last_insert_rowid()";
	}

	public  void saveRecord(Record obj)
	{
		SQLiteDatabase db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MyApp.getInstance().getApplicationContext().MODE_PRIVATE, null);  
		db.execSQL("INSERT INTO YH_RECORD(recordId,carId,parkId,entryTime,entryImg,entry,export,userId,state) VALUES (?,?,?,?,?,?,?,?,?)", new String[]{obj.recordId,obj.carId,obj.parkId,obj.entryTime,obj.entryImg,obj.entry,obj.export,obj.userId,obj.state});
		db.close();
	}
	 
	public  void saveFullRecord(Record obj)
	{
		SQLiteDatabase db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MyApp.getInstance().getApplicationContext().MODE_PRIVATE, null);  
		db.execSQL("INSERT INTO YH_RECORD(recordId,carId,parkId,entryTime,entryImg,entry,exportTime,exportImg,export,shouldAmt,userId,state) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", new String[]{obj.recordId,obj.carId,obj.parkId,obj.entryTime,obj.entryImg,obj.entry,obj.exportTime,obj.exportImg,obj.export,obj.userId,obj.state});
		db.close();
	}

	public  Parcelx queryParcel()
	{
         Parcelx info = null;
		 SQLiteDatabase db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MyApp.getInstance().getApplicationContext().MODE_PRIVATE, null);  	
		 Cursor c = db.rawQuery("SELECT * FROM YH_PORTAL", null);
	        while (c.moveToNext()) {  
	        	info = new Parcelx();
	        	info.name = c.getString(c.getColumnIndex("name"));
	        	info.id = c.getString(c.getColumnIndex("id"));
	        	//info.birth = c.getLong(c.getColumnIndex("birth"));
	        	break;
	        }  
	        c.close(); 
	        db.close();
		return info;
	}
 

	public Record queryRecord(String carId) {
		Record info = null;
		 SQLiteDatabase db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MyApp.getInstance().getApplicationContext().MODE_PRIVATE, null);  	
		 Cursor c = db.rawQuery("SELECT * FROM YH_RECORD where carId = ? and state < ?", new String[]{carId,"2"});
	        while (c.moveToNext()) {  
	        	info = mappingRecord(c);
	        	break;
	        }  
	        c.close(); 
	        db.close();
		return info;
	}

	public Record queryRecordById(String recordId) {
		Record info = null;
		 SQLiteDatabase db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MyApp.getInstance().getApplicationContext().MODE_PRIVATE, null);  	
		 Cursor c = db.rawQuery("SELECT * FROM YH_RECORD where recordId = ? ", new String[]{recordId});
	        while (c.moveToNext()) {  
	        	info = mappingRecord(c);
	        	 
	        	break;
	        }  
	        c.close(); 
	        db.close();
		return info;
	}

	public ArrayList<Record> queryRecordList(String account) {
		ArrayList<Record> recordList = new ArrayList<Record>();
		recordList.addAll(listOrderByEntryTime(account));
		recordList.addAll(listOrderByexportTime(account));
		return recordList;
	}

	public ArrayList<Record>  listOrderByEntryTime(String account) { 
		 ArrayList<Record> recordList = new ArrayList<Record>();
		 SQLiteDatabase db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MyApp.getInstance().getApplicationContext().MODE_PRIVATE, null);  
		 Cursor c;
		  c = db.rawQuery("SELECT * FROM YH_RECORD where userId = ? and state < ? order by entryTime desc", new String[]{account,"2"});
	        while (c.moveToNext()) {  
	        	recordList.add(mappingRecord(c));
	        }  
	        c.close(); 
	        db.close();
		return recordList;
	}

	public ArrayList<Record>  listOrderByexportTime(String account) {  
		 ArrayList<Record> recordList = new ArrayList<Record>();
		 SQLiteDatabase db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MyApp.getInstance().getApplicationContext().MODE_PRIVATE, null);  
		 Cursor c;
		  c = db.rawQuery("SELECT * FROM YH_RECORD where userId = ? and state > ? order by exportTime desc", new String[]{account,"1"});
	        while (c.moveToNext()) {  
	        	recordList.add(mappingRecord(c));
	        }  
	        c.close(); 
	        db.close();
		return recordList;
	}

	public ArrayList<Record>  queryLocalRecordList(String account) {
		 ArrayList<Record> recordList = new ArrayList<Record>();
		 SQLiteDatabase db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MyApp.getInstance().getApplicationContext().MODE_PRIVATE, null);  
		 Cursor c = db.rawQuery("SELECT * FROM YH_RECORD where userId = ? and state !='9' ", new String[]{account});
		 
	        while (c.moveToNext()) {  
	        	recordList.add(mappingRecord(c));
	        }  
	        c.close(); 
	        db.close();
		return recordList;
	}

	public void updateRecord(Record record) {
		SQLiteDatabase db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MyApp.getInstance().getApplicationContext().MODE_PRIVATE, null);
		db.execSQL("update YH_RECORD set exportTime=? ,exportImg=?,shouldAmt=?,export = ?,state=? where recordId=?", new String[]{record.exportTime,record.exportImg,record.shouldAmt,record.export,record.state,record.recordId});
		db.close();
	}

	public void updateRecordStatus(String recordId,String state) {
		SQLiteDatabase db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MyApp.getInstance().getApplicationContext().MODE_PRIVATE, null);
		db.execSQL("update YH_RECORD set state=?  where recordId=?", new String[]{state,recordId});
		db.close();
	}
	public void deleteRecord(String recordId) {
		SQLiteDatabase db = MyApp.getInstance().getApplicationContext().openOrCreateDatabase(DATABASE, MyApp.getInstance().getApplicationContext().MODE_PRIVATE, null);
		db.execSQL("delete from T_sunset_RECORD where recordId=?", new Object[]{recordId});		
		db.close();
	}
	
	private Record mappingRecord(Cursor c)
	{
		Record info=new Record();
    	info.carId = c.getString(c.getColumnIndex("carId"));
    	info.entryTime = c.getString(c.getColumnIndex("entryTime"));
    	info.entry = c.getString(c.getColumnIndex("entry"));
    	info.export = c.getString(c.getColumnIndex("export"));
    	info.parkId = c.getString(c.getColumnIndex("parkId"));
    	info.userId = c.getString(c.getColumnIndex("userId"));
    	info.entryImg = c.getString(c.getColumnIndex("entryImg"));
    	info.exportImg = c.getString(c.getColumnIndex("exportImg"));
    	info.exportTime = c.getString(c.getColumnIndex("exportTime"));
    	info.shouldAmt =  c.getString(c.getColumnIndex("shouldAmt"));
    	info.recordId =  c.getString(c.getColumnIndex("recordId"));
    	info.state = c.getString(c.getColumnIndex("state"));
    	return info;
	}





private MyDatabaseHelper dbHelper;
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
//开始组装第一条数据
values.put("name","The Da Vinci Code");
values.put("author","Dan Brown");
values.put("pages",454);
values.put("price",16.96);
db.insert("Book",null,values);//擂入第一条数据
values.clear();
//开始组装第二条数据
values.put("name","The Lost Symbol");
values.put("author","Dan Brown");
values.put("pages",510);
values.put("price",19.95);
db.insert("Book",null,values);//播入第二条数据
db .update("Book",values,"name=?", new String[] { "The Da Vinci Code" });

SQLiteDatabase db = dbHelper.getWritableDatabase();
db .delete("Book", "pages>?”，new String[]{"598"});
 */


